import time
import pymysql

def get_time():
    time_str = time.strftime("%Y{}%m{}%d{} %X")
    return time_str.format("年","月","日")


def get_conn():
    # 建立连接
    conn = pymysql.connect(host="localhost", user="root", password="123456", db="cov", charset="utf8")
    # c创建游标A
    cursor = conn.cursor()
    return conn, cursor


def close_conn(conn, cursor):
    if cursor:
        cursor.close()
    if conn:
        conn.close()

def query(sql,*args):
    """

    :param sql:
    :param args:
    :return:
    """
    conn,cursor = get_conn()
    cursor.execute(sql,args)
    res = cursor.fetchall()
    close_conn(conn,cursor)
    return res

def test():
    sql = "select * from details"
    res = query(sql)
    return res[0]

def get_chinaTotal():
    conn, cursor = get_conn()
    sql = "SELECT confirm,nowConfirm,heal,dead,suspect,update_time FROM chinatotal order by id desc"
    cursor.execute(sql)
    res = cursor.fetchone()
    close_conn(conn, cursor)
    return res

#全国疫情实时跟踪数据
""""" 
查询总确诊数,最新日期的疑似,总治愈数,总死亡数 
SELECT SUM(confirm),
    (SELECT suspect FROM history ORDER BY ds DESC LIMIT 1),
    SUM(heal),SUM(dead) FROM details
    WHERE update_time=(SELECT update_time FROM details ORDER BY update_time DESC LIMIT 1)
"""""
def get_c1_data():
    sql = "select sum(confirm)," \
          "(select suspect from history order by ds desc limit 1)," \
          "sum(heal),sum(dead) from details " \
          "where update_time=(select update_time from details order by update_time desc limit 1) "
    res = query(sql)
    return res[0]
"""""
查询每个省的确诊人数
SELECT province,SUM(confirm) FROM details
          WHERE update_time=(SELECT update_time FROM details
          ORDER BY update_time DESC LIMIT 1)
          GROUP BY province
"""""
def get_c2_data():
    sql = "select province,sum(confirm) from details " \
          "where update_time=(select update_time from details " \
          "order by update_time desc limit 1) " \
          "group by province"
    res = query(sql)
    return res

'''''
查询历史表  ds:时间   confirm:确诊  suspect:疑似病例  heal:治愈 dead:死亡
select ds,confirm,suspect,heal,dead from history
'''''
def get_l1_data():
    sql = "select ds,confirm,suspect,heal,dead from history"
    res = query(sql)
    return res
'''''
查询历史表  ds:时间   confirm_add:新增确诊  suspect_add:新增疑似病例  
select ds,confirm_add,suspect_add from history
'''''
def get_l2_data():
    sql = "select ds,confirm_add,suspect_add from history"
    res = query(sql)
    return res

'''''
查询除("台湾","香港","澳门")外的大陆城市数据  包括("北京","上海","天津","重庆")直辖市
 SELECT city,confirm FROM
            (SELECT city,confirm FROM details
            WHERE update_time=(SELECT update_time FROM details ORDER BY update_time DESC LIMIT 1)
            AND province NOT IN ("台湾","香港","澳门")
            UNION ALL
            SELECT province AS city,SUM(confirm) AS confirm FROM details
            WHERE update_time=(SELECT update_time FROM details ORDER BY update_time DESC LIMIT 1)
            AND province IN ("北京","上海","天津","重庆") GROUP BY province) AS a
            ORDER BY confirm DESC LIMIT 5
'''''
def get_r1_data():
    sql = 'select city,confirm from ' \
          '(select city,confirm from details ' \
          'where update_time=(select update_time from details order by update_time desc limit 1) ' \
          'and province not in ("台湾","香港","澳门")  ' \
          'union all ' \
          'select province as city,sum(confirm) as confirm from details ' \
          'where update_time=(select update_time from details order by update_time desc limit 1) ' \
          'and province in ("北京","上海","天津","重庆") group by province) as a ' \
          'order by confirm desc limit 5'
    res = query(sql)
    return res

def get_r2_data():
    sql = "select content from hotsearch order by id desc limit 20"
    res = query(sql)
    return res

if __name__ == "__main__":
    print(get_r2_data())
    #print(test())